<?php
define('DB_QUERY_EXECUTE', 1);
define('DB_QUERY_ROW', 2);
define('DB_QUERY_ROWS', 3);

define('DB_DATA_STR', 'str');
define('DB_DATA_INT', 'int');
define('DB_DATA_SYS', 'sys');
define('DB_DATA_FLOAT', 'float');
define('DB_DATA_BOOL', 'bool');

class Db_mysqli {
    
    private $hostname;
    private $username;
    private $password;
    private $database;
    private $port;
    
    private $charset = 'utf8';
    private $collate = 'utf8_general_ci';
    
    private $conn_id;
    private $trans_depth = 0;
    
    private $auto_connect = true;
    private $real_escape = false;
    
    public static $IS_DEBUG = false;
    public static $query_stack = array(); 
    
    public function __construct($opts) {
        if ( ! isset($opts['hostname'])) {
            $this->error('hostname cannot be none');
        }
        if ( ! isset($opts['username'])) {
            $this->error('username cannot be none');
        }
        if ( ! isset($opts['password'])) {
            $this->error('password cannot be none');
        }
        if ( ! isset($opts['database'])) {
            $this->error('database cannot be none');
        }
        $this->hostname = $opts['hostname'];
        $this->username = $opts['username'];
        $this->password = $opts['password'];
        $this->database = $opts['database'];
        
        if (isset($opts['charset'])) {
            $this->charset = $opts['charset'];
        }
        if (isset($opts['collate'])) {
            $this->collate = $opts['collate'];
        }
        if (isset($opts['port'])) {
            $this->port = $opts['port'];
        }
        if (isset($opts['auto_connect'])) {
            $this->auto_connect = $opts['auto_conenct'];
        }
        if (isset($opts['real_escape'])) {
            $this->real_escape = (bool) $opts['real_escape'];
        }
        
        if ($this->auto_connect) {
            $this->connect();
        }
    }
    
    
    
    public function connect() {
        if ( ! isset($this->conn_id)) {
            if ($this->port) {
                $this->conn_id = mysqli_connect($this->hostname, $this->username, $this->password, $this->database, $this->port);
            } else {
                $this->conn_id = mysqli_connect($this->hostname, $this->username, $this->password, $this->database);
            }
            if (mysqli_connect_errno()) {
                $this->error(mysqli_connect_error());
            }
            $this->execute("SET NAMES '" . $this->escape_str($this->charset) . "' COLLATE '" . $this->escape_str($this->collate) . "'");
        }
    }
    
    
    public function trans_start() {
        $this->trans_depth++;
        if ($this->trans_depth === 1) {
            $this->execute('SET AUTOCOMMIT=0');
            $this->execute('START TRANSACTION');
            return true;
        } else {
            return false;
        }
    }
    
    
    public function trans_commit() {
        $this->trans_depth--;
        if ($this->trans_depth === 0) {
            $this->execute('COMMIT');
            $this->execute('SET AUTOCOMMIT=1');
            return true;
        } else {
            return false;
        }
    }
    
    
    public function trans_rollback() {
        if ($this->trans_depth > 0) {
            $this->execute('ROLLBACK');
            $this->execute('SET AUTOCOMMIT=1');
            $this->trans_depth = 0;
            return true;
        }
        return false;
    }
    
    
    public function query($sql, $type = DB_QUERY_EXECUTE) {
        $result_id = mysqli_query($this->conn_id, $sql);
        $errno = mysqli_errno($this->conn_id);
        
        if ($errno > 0) {
            var_dump($result_id);
            var_dump($errno);
            echo mysqli_error($this->conn_id);
            echo $sql; exit();
            $this->error("[execute] $sql"); 
            return false;
        }
        if ($type === DB_QUERY_EXECUTE) {
            $result = true;
        } else if ($type === DB_QUERY_ROW) {
            $result = $this->_get_row($result_id);
        } else if ($type === DB_QUERY_ROWS) {
            $result = $this->_get_rows($result_id);
        }
        
        if (is_resource($result_id)) {
            mysqli_free_result($result_id);
        }
        if (self::$IS_DEBUG) {
            self::$query_stack[] = $sql;
        }
        return $result;
    }
    
    
    public function execute($sql) {
        return $this->query($sql, DB_QUERY_EXECUTE);
    }
    
    
    
    public function row($sql) {
        return $this->query($sql, DB_QUERY_ROW);
    }
    
    
    public function rows($sql) {
        return $this->query($sql, DB_QUERY_ROWS);
    }
    
    
    
    public function select_row($table, $columns = array(), $where = array(), $where_format = array()) {
        return $this->_select(DB_QUERY_ROW, $table, $columns, $where, $where_format);
    }
    
    
    public function select_rows($table, $columns = array(), $where = array(), $where_format = array(), 
            $limit = 20, $offset = 0) {
        return $this->_select(DB_QUERY_ROWS, $table, $columns, $where, $where_format, $limit, $offset);
    }
    
    
    private function _select($type, $table, $columns = array(), $where = array(), $where_format = array(), 
            $limit = 20, $offset = 0) {
        if ($columns) {
            $columns_str = '`' . implode('`,`', $columns) . '`';
        } else {
            $columns_str = '*';
        }
        
        $wheres_str = '';
        if ($where) {
            foreach ($where as $wk => $wv) {
                if (isset($where_format[$wk])) {
                    $wv = $this->_format($wv, $where_format[$wk]);
                } else {
                    $wv = "'" . $this->escape_str($wv) . "'";
                }
                $wheres[] = "`$wk`=$wv";
            }
            $wheres_str = ' WHERE ' . implode(' AND ', $wheres);
        }
        $sql = "SELECT {$columns_str} FROM `{$table}`{$wheres_str}";
        
        if ($type === DB_QUERY_ROWS) {
            $limit = (int) $limit;
            $offset = (int) $offset;
            $limit = ($limit >= 0) ? $limit : 20;
            $sql .= " LIMIT $offset, $limit";
            return $this->rows($sql);
        } else {
            $sql .= ' LIMIT 1';
            return $this->row($sql);
        }
    }
    
    
    public function insert($table, $data, $format = null) {
        
        $fields = array();
        $values = array();
        
        foreach ($data as $k => $v) {
            $fields[] = "`$k`";
            if (isset($format[$k])) {
                $v = $this->_format($v, $format[$k]);
            } else {
                $v = "'" . $this->escape_str($v) . "'";
            }
            $values[] = $v;
        }
        $fields_str = implode(',', $fields);
        $values_str = implode(',', $values);
        $sql = "INSERT INTO `{$table}` ({$fields_str}) VALUES ({$values_str})";
        
        if (true === $this->execute($sql)) {
            return $this->insert_id();
        } else {
            return false;
        }
    }
    
    
    public function insert_id() {
        return mysqli_insert_id($this->conn_id);
    }
    
    
    public function update($table, $data, $where, $format = array(), $where_format = array()) {
        
        $sets = array();
        $wheres = array();
        
        foreach ($data as $k => $v) {
            if (isset($format[$k])) {
                $v = $this->_format($v, $format[$k]);
            } else {
                $v = "'" . $this->escape_str($v) . "'";
            }
            $sets[] = "`$k`=$v";
        }
        foreach ($where as $wk => $wv) {
            if (isset($where_format[$wk])) {
                $wv = $this->_format($wv, $where_format[$wk]);
            } else {
                $wv = "'" . $this->escape_str($wv) . "'";
            }
            $wheres[] = "`$wk`=$wv";
        }
        
        $sets_str = implode(',', $sets);
        $wheres_str = implode(' AND ', $wheres);
        
        $sql = "UPDATE `{$table}` SET {$sets_str} WHERE {$wheres_str}";
        
        return $this->execute($sql);
    }
    
    
    public function delete($table, $where, $where_format = array()) {
        $arr_wheres = array();
        foreach ($where as $wk => $wv) {
            if (isset($where_format[$wk])) {
                $wv = $this->_format($wv, $where_format[$wk]);
            } else {
                $wv = "'" . $this->escape_str($wv) . "'";
            }
            $arr_wheres = "`$wk`=$wv";;
        }
        $wheres_str = implode(' AND ', $arr_wheres);
        $sql = "DELETE  FROM `{$table}` WHERE {$wheres_str}";
        return $this->execute($sql);
    }
    
    
    private function _format($v, $type = DB_DATA_STR) {
        $rs = $v;
        if ($type === DB_DATA_BOOL) {
            $rs = (bool) $v;
        } else if ($type === DB_DATA_INT) {
            $rs = (int) $v;
        } else if ($type === DB_DATA_FLOAT) {
            $rs = (float) $v;
        } else if ($type === DB_DATA_SYS) {
            $rs = $v;
        } else {
            $rs = "'" . $this->escape_str($v) . "'";
        }
        return $rs;
    }
    
    private function _get_row($result_id) {
        $row = mysqli_fetch_assoc($result_id);
        return $row ? $row : false;
    }
    
    
    private function _get_rows($result_id) {
        $rows = array();
        while ($row = mysqli_fetch_assoc($result_id)) {
            if (isset($row['id'])) {
                $rows[$row['id']] = $row;
            } else {
                $rows[] = $row;
            }
        }
        return $rows;
    }
    
    
    public function escape($data, $real = false) {
        if (is_array($data)) {
            foreach ((array) $data as $k => $v) {
                if (is_array($v)) {
                    $data[$k] = $this->escape($v);
                } else {
                    $data[$k] = $this->escape_str($v, $real);
                }
            }
        } else {
            $data = $this->escape_str($data, $real);
        }
        return $data;
    }
    
    
    public function escape_str($str, $real = false) {
        if ($real || $this->real_escape) {
            if (function_exists('mysqli_real_escape_string') && is_object($this->conn_id)) {
                $str = mysqli_real_escape_string($this->conn_id, $str);
            } else if (function_exists('mysql_escape_string')) {
                $str = mysql_escape_string($str);
            } else {
                $str = addslashes($str);
            }
        } else {
            $str = addslashes($str);
        }
        return $str;
    }
    
    
    
    
    
    
    public function error($msg = '') {
        $this->trans_rollback();
        echo $msg . "\r\n";
        echo mysqli_error($this->conn_id) . "\r\n";
        debug_print_backtrace();
        die();
    }
    
    
    public function close() {
        mysqli_close($this->conn_id);
    }
    
    
    public function __destruct() {
        $this->close();
    }
}
?>